package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleDoctornewService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleDoctornewServiceImpl implements IGetTotleDoctornewService {
    public String getsqlDepartmentnew(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sqlb = "";
        String performance = "hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())) {
                sqlb += "";
            } else {
                sqlb += " and b.treatment_unit='" + searchInfo.getDimensionSelectionCustomerAll() + "'";
            }
            performance = "section_performance";
        }
        String sql = "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.customer_status='新客首次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.isdeal_status='已成交' and b.customer_status='新客首次'  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and " + condition + sqlb + " group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t." + performance + ") from(select c." + performance + " from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " and b.customer_status='新客首次' group by c.id) t),0.00)  as departmentPerformance ," +
                " 0 as tcPerformance  from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.customer_status='新客多次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.isdeal_status='已成交' and b.customer_status='新客多次'  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and " + condition + sqlb + " group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t." + performance + ") from(select c." + performance + " from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " and b.customer_status='新客多次' group by c.id) t),0.00)  as departmentPerformance ," +
                " 0 as tcPerformance  from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.customer_status in ('新客首次','新客多次') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and " + condition + sqlb + " group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t." + performance + ") from(select c." + performance + " from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00)  as departmentPerformance ," +
                " 0 as tcPerformance  from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.customer_status='老客' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.isdeal_status='已成交' and b.customer_status='老客'  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and " + condition + sqlb + " group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t." + performance + ") from(select c." + performance + " from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " and b.customer_status='老客' group by c.id) t),0.00)  as departmentPerformance ," +
                " 0 as tcPerformance  from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.customer_status in ('新客首次','新客多次','老客') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次','老客')  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and " + condition + sqlb + " group by b.reservations_id) t)  AS volume," +
                "ifnull((select sum(t." + performance + ") from(select c." + performance + " from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and " + condition + sqlb + " and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00)  as departmentPerformance ," +
                " 0 as tcPerformance  from dual";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }
}